﻿CREATE PROCEDURE [dbo].[_tDocs_Get]
WITH EXECUTE AS CALLER
AS
DECLARE @docHandle int
DECLARE @xmlDocument xml -- or xml type
SET @xmlDocument = 
'<VFPData><row oper_id="220" ddate="2007-08-29" dpay="2007-08-29" rate="" db="1410000400" cr="1610000100" orders_id="_262119DIY" prodpp_id="_23J14NJZ8" scount="1.0000" priceg="1.0059" priceu="0" scountd="1.0000" vg="1.0059" vu="1.0059" discount="0.0000" vat="0.1800" retsales_id="" mp_id="" cuser="asa"/><row oper_id="220" ddate="2007-08-29" dpay="2007-08-29" rate="" db="1410000400" cr="1610000100" orders_id="_262119EMT" prodpp_id="_23J14NJZ8" scount="1.0000" priceg="1.0059" priceu="0" scountd="1.0000" vg="1.0059" vu="1.0059" discount="0.0000" vat="0.1800" retsales_id="" mp_id="" cuser="asa"/><row oper_id="220" ddate="2007-08-29" dpay="2007-08-29" rate="" db="1410000400" cr="1610000100" orders_id="_262119FHS" prodpp_id="_23J14NJZ8" scount="1.0000" priceg="1.0059" priceu="0" scountd="1.0000" vg="1.0059" vu="1.0059" discount="0.0000" vat="0.1800" retsales_id="" mp_id="" cuser="asa"/></VFPData>'

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
-- Use OPENXML to provide rowset consisting of customer data.
SELECT * 
FROM OPENXML(@docHandle, N'/VFPData/row') 
WITH (
[oper_id] char(3),
[ddate] datetime,
[dpay] datetime,
[rate] money,
[db] char(10),
[cr] char(10),
[orders_id] char(10),
[prodpp_id] char(10),
[scount] money,
[priceg] money,
[priceu] money,
[scountd] money,
[vg] money,
[vu] money,
[discount] decimal(4, 3) ,
[vat] decimal(3, 2) ,
[retsales_id] varchar(10),
[mp_id] char(10),
[cuser] varchar(10)
)

/*
ss=cursortoxml(1,'xx',3,0,0,"")
ss=cursortoxml(1,'xx',3,0,0,"1")
_cliptext= xx
*/
-- Remove the internal representation of the XML document.
--INSERT INTO pp
EXEC sp_xml_removedocument @docHandle


